<?php
class SalesmanController extends Zend_Controller_Action
{
	public function operatingreportAction(){
	// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		//get current user information
		$auth = Zend_Auth::getInstance();
		$user = $auth->getIdentity();

		$this->view->title = "Salesman operating report";
		// create form
		$reportForm = new ReportForm();

		// remove the salesman control
		$reportForm->removeElement('salesmanReportForm');

		// populate the agent control
		$salesman_id = $user->getId();
		$users = new Users();
		$select = $users->getAdapter()->select()
									->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
						            ->joinleft(array('t' => 'vmg_user_type'),
						                        'u.type_id = t.id', array('type_name'))
						            ->where('t.access_level = ' . $access_levels->mobishop_level)
						            ->where('u.managed_by = ?', (int)$salesman_id);
		$rows = $users->getAdapter()->fetchAll($select);
		if(count($rows)){
			$reportForm->getElement("agentReportForm")->addMultiOption(-1, $translator->_('All'));
			foreach($rows as $row){
				$reportForm->getElement("agentReportForm")->addMultiOption($row['agent_id'], $row['agent_name']);
			}
		}
		$exportButton = $reportForm->getElement('exportReportForm');

		// remove the salesman and agent selection controls
		// $reportForm->removeElement('salesmanReportForm');
		// $reportForm->removeElement('agentReportForm');
		$show_flag = true;
		$page = 1;

		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			$agent_id = $formData["agentReportForm"];
			if($salesman_id != null){
				$users = new Users();
				$select = $users->getAdapter()->select()
										->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
							            ->joinleft(array('t' => 'vmg_user_type'),
							                        'u.type_id = t.id', array('type_name'))
							            ->where('t.access_level = ' . $access_levels->mobishop_level)
							            ->where('u.managed_by = ?', (int)$salesman_id);
				$rows = $users->getAdapter()->fetchAll($select);
				$reportForm->getElement('agentReportForm')->addMultiOption(-1, $translator->_('All'));
				foreach($rows as $row){
					$reportForm->getElement('agentReportForm')->addMultiOption($row['agent_id'], $row['agent_name']);
				}
				$reportForm->getElement('agentReportForm')->setValue($agent_id);
			}
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				//$salesman_id = mysql_escape_string($reportForm->getValue('salesmanReportForm'));
				$agent_id = mysql_escape_string($reportForm->getValue('agentReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			$agent_id = mysql_escape_string($this->_request->getParam('agentReportForm'));
			// fetch rows to show
			$page = $this->_getParam('page',1);
			if($salesman_id != null){
				$users = new Users();
				$select = $users->getAdapter()->select()
										->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
							            ->joinleft(array('t' => 'vmg_user_type'),
							                        'u.type_id = t.id', array('type_name'))
							            ->where('t.access_level = ' . $access_levels->mobishop_level)
							            ->where('u.managed_by = ?', (int)$salesman_id);
				$rows = $users->getAdapter()->fetchAll($select);
				$reportForm->getElement('agentReportForm')->addMultiOption(-1, $translator->_('All'));
				foreach($rows as $row){
					$reportForm->getElement('agentReportForm')->addMultiOption($row['agent_id'], $row['agent_name']);
				}
				$reportForm->getElement('agentReportForm')->setValue($agent_id);
			}
		}

		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year,
				'salesmanReportForm' => $salesman_id,
				'agentReportForm' => $agent_id
			);
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$billing_table = 'vmg_billing_count' . $year . $month;
			$select = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->where('u.managed_by = ?', $salesman_id)
								->order('date DESC')
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;


			$select_sum = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array( "sum(mo_count) as total_mo", "sum(mt_count) as total_mt", "sum(cdr_count) as total_mp", "sum(money) as total_money"))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->group(array('u.managed_by'))
								->where('u.managed_by = ?', $salesman_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;

			$select_count = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array( "count(*) as num_rows"))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->group(array('u.managed_by'))
								->where('u.managed_by = ?', $salesman_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;
			// check whether or not a agent_id is specified
			if($agent_id != -1){
				$select = $select->where('u.id = ?', (int)$agent_id);
				$select_sum = $select_sum->where('u.id = ?', (int)$agent_id);
				$select_count = $select_count->where('u.id = ?', (int)$agent_id);
			}

			if($show_flag){	// show the result
				try{
					$row = $users->getAdapter()->fetchRow($select_sum);
				}catch(Zend_Db_Statement_Exception $e){
					$exportButton->setValue('Export data');
					$reportForm->getElement('submitReportForm')->setValue("Show report");
					$this->view->form = $reportForm;
					return;
				}
				$total_amount = $row["total_money"];
				$total_mo = $row['total_mo'];
				$total_mt = $row['total_mt'];
				$total_mp = $row['total_mp'];
				$this->view->total_amount = $total_amount;
				$this->view->total_mo = $total_mo;
				$this->view->total_mt = $total_mt;
				$this->view->total_mp = $total_mp;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);
			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
                 try{
					$row = $users->getAdapter()->fetchRow($select_count);
                 }catch(Zend_Db_Statement_Exception $e){
					$exportButton->setValue('Export data');
					$reportForm->getElement('submitReportForm')->setValue("Show report");
					$this->view->form = $reportForm;
					return;
				 }
				$num_records = $row["num_rows"];
				$select_limit =$select = $users->getAdapter()->select()
							->from(array( 'bt' => $billing_table))
							->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
							->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
							->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
							->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
							->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
							->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
							->where('u.managed_by = ?', $salesman_id)
							->order(array('salesman_name', 'date'))
							->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
							;
				// check to see whether we will include all agents
				if($agent_id != -1){
					$select_limit = $select->where('u.id = ?', (int)$agent_id);
				}
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					$i = $num_records/1000;
				 	$tmp = array(
				 		$translator->_("Ngay"),
				 		$translator->_("Gio"),
				 		$translator->_("Di dong"),
				 		$translator->_("Dai ly"),
				 		$translator->_("Dau so"),
						$translator->_("So luong MO"),
						$translator->_("So luong MT"),
						$translator->_("So luong MP"),
						$translator->_("Doanh thu tam tinh")
					);
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);
						foreach($rows as $row){
							$row['msisdn'] = '\'' . str_replace("+84", "0", $row['msisdn']);
							echo substr($row['date'], 8,2) . ",";
							echo substr($row['date'], 11, 2) . ",";
							echo $row['msisdn']. ",";
							echo $row['agent_name']. ",";
							echo $row['short_code']. ",";
							echo $row['mo_count']. ",";
							echo $row['mt_count']. ",";
							echo $row['cdr_count']. ",";
							echo $row['money'];
							echo "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;
	}

	public function customerreportAction(){
		// set title
		$this->view->title = "Customer development reports";

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		//get current user information
		$auth = Zend_Auth::getInstance();
		$user = $auth->getIdentity();
		$salesman_id = $user->getId();

		// create form
		$reportForm = new ReportForm();
		$exportButton = $reportForm->getElement('exportReportForm');

		// remove the salesman and agent selection controls
		$reportForm->removeElement('salesmanReportForm');

		// populate the agent control
		$users = new Users();
		$select = $users->getAdapter()->select()
									->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
						            ->joinleft(array('t' => 'vmg_user_type'),
						                        'u.type_id = t.id', array('type_name'))
						            ->where('t.access_level = ' . $access_levels->mobishop_level)
						            ->where('u.managed_by = ?', (int)$salesman_id);
		$rows = $users->getAdapter()->fetchAll($select);
		if(count($rows)){
			$reportForm->getElement("agentReportForm")->addMultiOption(-1, $translator->_('All'));
			foreach($rows as $row){
				$reportForm->getElement("agentReportForm")->addMultiOption($row['agent_id'], $row['agent_name']);
			}
		}
		$show_flag = true;
		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				$agent_id = mysql_escape_string($reportForm->getValue('agentReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			$agent_id = mysql_escape_string($this->_request->getParam('agentReportForm'));
			// fetch rows to show
			$page = $this->_getParam('page',1);
		}
		// generate reports
		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year,
				'agentReportForm' => $agent_id
			);
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$select = $users->getAdapter()->select()
								->from(array('su' => 'vmg_service_user'))
								->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
								->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.name as agent_name'))
								->where('u.managed_by = ?', (int)$salesman_id)
								->order(array('activ_date DESC'))
								;
			$select_count = $users->getAdapter()->select()
								->from(array('su' => 'vmg_service_user'), array('count(su.msisdn) as num_rows'))
								->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id', array())
								->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array())
								->where('u.managed_by = ?', (int)$salesman_id)
								;
			if($agent_id != -1){
				$select = $select->where('u.id = ?', (int)$agent_id);
				$select_count = $select_count->where('u.id = ?', (int)$agent_id);
			}
			if($show_flag){	// show the result
				$row = $users->getAdapter()->fetchRow($select_count);
				$num_customers = $row["num_rows"];
				$this->view->num_customers = $num_customers;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);
			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
					$row = $users->getAdapter()->fetchRow($select_count);
					$num_records = $row["num_rows"];
					$select_limit = $users->getAdapter()->select()
									->from(array('su' => 'vmg_service_user'))
									->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
									->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
									->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.name as agent_name'))
									->where('u.managed_by = ?', (int)$salesman_id)
									->order(array('activ_date DESC'))
									;
					if($agent_id !== -1){
						$select_limit = $select_limit->where('u.id = ?', $agent_id);
					}
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					$i = $num_records/1000;
				 	$tmp = array(
						$translator->_("Ngay kich hoat"),
				 		$translator->_("Gio kich hoat"),
				 		$translator->_("Thue bao"),
				 		$translator->_("Dai ly"),
						$translator->_("Hang SX"),
						$translator->_("Loai DT"),
						$translator->_("Chu thue bao"),
						$translator->_("Thanh pho"),
						$translator->_("Dia chi"),
						$translator->_("Email")
					);
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);

						foreach($rows as $row){
							echo substr($row['activ_date'], 8,2) . ",";
							echo substr($row['activ_date'], 11, 2) . ",";
							$row['msisdn'] = str_replace("+84", "'0", $row['msisdn']);
							echo $row['msisdn'] . ",";
							echo str_replace(",", "-",$row['agent_name']) . ",";
							echo str_replace(",", "-",$row['device_brand']) . ",";
							echo $row['device_model'] . ",";
							echo $row['fullname'] . ",";
							echo str_replace(",", "-",$row['city']) . ",";
							echo str_replace(",", "-",$row['address']) . ",";
							echo $row['email'];
							echo  "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;
	}

}
